package org.oexam.ostudy.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.TreeSet;

import javax.naming.NamingException;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.oexam.ostudy.facade.struct.TestNameAndId;
import org.oexam.ostudy.misc.GeneralConstants;
import org.oexam.ostudy.struct.Administrators;
import org.oexam.ostudy.struct.Students;
import org.oexam.ostudy.struct.Teachers;
import org.oexam.ostudy.struct.Users;

public class UsersAccess {

	public Users readUser(String userName) {

		Users user = new Users();

		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		String sqlQuery = "select users0_.user_name as col_0_0_, users0_.password as col_1_0_, users0_.user_id as col_2_0_ from Users users0_ where users0_.user_name=\'"
				+ userName + "\'";

		Query query = (Query) session.createQuery(sqlQuery);
		int id = -1;

		for (Iterator it = ((org.hibernate.Query) query).iterate(); it
				.hasNext();) {
			Object[] row = (Object[]) it.next();
			id = (Integer) row[2];			
		}

		if (id != -1) {
			user = (Users) session.get(Users.class, id);

		} else {
			user = null;
		}

		session.close();


		return user;
	}
	
	public Administrators readAdministratorDetails(Users user){
		
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		String sqlQuery = "select users0_.user_name as col_0_0_, users0_.password as col_1_0_, users0_.user_id as col_2_0_ from Users users0_ where users0_.user_name=\'"
				+ user.getUser_id() + "\'";

		Query query = (Query) session.createQuery(sqlQuery);
		int id = -1;

		for (Iterator it = ((org.hibernate.Query) query).iterate(); it
				.hasNext();) {
			Object[] row = (Object[]) it.next();
			id = (Integer) row[2];
			System.out.println("ID: " + row[0]);
		}

		if (id != -1) {
			user = (Users) session.get(Users.class, id);

		}

		session.close();
		return null;
	}

	public Users readAllUsers() {

		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		String sqlQuery = "select users0_.user_name as col_0_0_, users0_.password as col_1_0_ from Users users0_ ";

		Query query = (Query) session.createQuery(sqlQuery);

		for (Iterator it = ((org.hibernate.Query) query).iterate(); it
				.hasNext();) {
			Object[] row = (Object[]) it.next();
			// System.out.println("ID: " + row[0]);
			// System.out.println("Name: " + row[1]);

		}

		session.close();

		return null;
	}
	
	public Users getUser(Users user){
	
		
		
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		
		String SQL_QUERY = "select users0_.user_id as col_0_0_, users0_.user_name as col_1_0_  from Users users0_ ";
		Query query = (Query) session.createQuery(SQL_QUERY);

		TreeSet<Integer> sortedUserId = new TreeSet<Integer>();
		TreeSet<String>  sortedUserName = new TreeSet<String>();  
		for (Iterator it = ((org.hibernate.Query) query).iterate(); it
				.hasNext();) {
			
			Object[] row = (Object[]) it.next();
			sortedUserId.add((Integer)row[0]);
			sortedUserName.add((String)row[1]);
		}
		session.close();
		
		if(sortedUserName.contains(user.getUser_name())) {
			return null;
		}
		
		int userId = sortedUserId.last()+1;
		user.setUser_id(userId);		
				
		return user;
	}
	
	public boolean insertAdministrator(Administrators admin, Users user){
		
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		
		String SQL_QUERY = "select administra0_.admin_id as col_0_0_, administra0_.first_name as col_1_0_ from Administrators administra0_";
		Query query = (Query) session.createQuery(SQL_QUERY);

		TreeSet<Integer> sortedAdminId = new TreeSet<Integer>();
		  
		for (Iterator it = ((org.hibernate.Query) query).iterate(); it.hasNext();) {
			
			Object[] row = (Object[]) it.next();
			sortedAdminId.add((Integer)row[0]);			
		}
	
		if (sortedAdminId.isEmpty())  {
			return false;
		}
		
		int adminId = sortedAdminId.last()+1;
		admin.setAdmin_id(adminId);		
		session.save(user);
		session.save(admin);
		
		session.getTransaction().commit();
		session.close();
		
		
		return true;
	}
	
	
	public boolean insertTeacher(Teachers teacher, Users user){

		
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		
		String SQL_QUERY = "select teacher0_.teacher_id as col_0_0_, teacher0_.first_name as col_1_0_ from Teachers teacher0_";
		Query query = (Query) session.createQuery(SQL_QUERY);

		TreeSet<Integer> sortedTeacherId = new TreeSet<Integer>();
		  
		for (Iterator it = ((org.hibernate.Query) query).iterate(); it.hasNext();) {
			
			Object[] row = (Object[]) it.next();
			sortedTeacherId.add((Integer)row[0]);			
		}
	
		if (sortedTeacherId.isEmpty())  {
			return false;
		}
		
		int teacherId = sortedTeacherId.last()+1;
		teacher.setTeacher_id(teacherId);		
		session.save(user);
		session.save(teacher);
		
		session.getTransaction().commit();
		session.close();
		
		return true;
	}
	
	public boolean insertStudent(Students student, Users user){

		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		
		String SQL_QUERY = "select student0_.student_id as col_0_0_, student0_.first_name as col_1_0_ from Students student0_";
		Query query = (Query) session.createQuery(SQL_QUERY);

		TreeSet<Integer> sortedStudentId = new TreeSet<Integer>();
		
		for (Iterator it = ((org.hibernate.Query) query).iterate(); it.hasNext();) {
			
			Object[] row = (Object[]) it.next();
			sortedStudentId.add((Integer)row[0]);			
		}
	
		// this needs to be removed if 0 assign 1 as id
		if (sortedStudentId.isEmpty())  {
			return false;
		}
		
		int studentId = sortedStudentId.last()+1;
		student.setStudent_id(studentId);		
		session.save(user);
		session.save(student);
		
		session.getTransaction().commit();
		session.close();
		
		
		return true;
	}
	
	public List<Students> getStudents() {
		List<Students> studentList = new ArrayList<Students>();
		
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		

		String SQL_QUERY = "select student0_.student_id as col_0_0_, student0_.first_name as col_1_0_, student0_.last_name as col_2_0_ from Students student0_";
		Query query = (Query) session.createQuery(SQL_QUERY);

        for (Iterator it = ((org.hibernate.Query) query).iterate(); it.hasNext();) {
			
			Object[] row = (Object[]) it.next();
			int student_id = (Integer)row[0];
			Students student = (Students) session.get(Students.class, student_id);
			studentList.add(student);
		}
        
        session.close();
		
		
		
		return studentList;
	}
	
	public List<Administrators> getAdministrators() {
        List<Administrators> adminList = new ArrayList<Administrators>();
		
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		
		String SQL_QUERY = "select admin0_.admin_id as col_0_0_, admin0_.first_name as col_1_0_, admin0_.last_name as col_2_0_ from Administrators admin0_";
		Query query = (Query) session.createQuery(SQL_QUERY);
		
        for (Iterator it = ((org.hibernate.Query) query).iterate(); it.hasNext();) {
			
			Object[] row = (Object[]) it.next();
			int admin_id = (Integer)row[0];
			Administrators admin = (Administrators) session.get(Administrators.class, admin_id);
			adminList.add(admin);
		}
		
		session.close();
		
		return adminList;
	}
	
	public List<Teachers> getTeachers() {
        List<Teachers> teacherList = new ArrayList<Teachers>();
        
        SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		
		String SQL_QUERY = "select teacher0_.teacher_id as col_0_0_, teacher0_.first_name as col_1_0_, teacher0_.last_name as col_2_0_ from Teachers teacher0_";
		Query query = (Query) session.createQuery(SQL_QUERY);
        
        for (Iterator it = ((org.hibernate.Query) query).iterate(); it.hasNext();) {
			
			Object[] row = (Object[]) it.next();
			int teacher_id = (Integer)row[0];
			Teachers teacher = (Teachers) session.get(Teachers.class, teacher_id);
			teacherList.add(teacher);
		}
        
        session.close();
		
        return teacherList;
	}
	
	public Users getUser(int user_id) {
		 
        SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		Users user = (Users)session.get(Users.class, user_id);
		session.close();
		
		return user;
	}
	
	
	
	public Students getStudent(int student_id) {
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		Students student = (Students)session.get(Students.class, student_id);
		session.close();
		
		return student;		
	}
	
	public Students getStudentUsingJDBC(int student_id) throws SQLException, NamingException {

		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery("select * from Students where student_id="
						+ student_id);

		Students student = new Students();
		while (rs.next()) {

			student.setStudent_id(rs.getInt("student_id"));
			student.setAddress(rs.getString("address"));
			student.setEmail(rs.getString("email"));
			student.setFirst_name(rs.getString("first_name"));
			student.setLast_name(rs.getString("last_name"));
			student.setPhone(rs.getString("phone"));
			student.setTeacher_responsible(rs.getInt("teacher_responsible"));
			student.setUser_id(rs.getInt("user_id"));

		}
		rs.close();

		return student;
	}
	
	public Teachers getTeacher(int teacher_id) {
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		Teachers teacher = (Teachers)session.get(Teachers.class, teacher_id);
		session.close();
		
		return teacher;	
		
	}	
	
	public Administrators getAdmin(int admin_id){
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();
		Administrators admin = (Administrators)session.get(Administrators.class, admin_id);
		session.close();
		return admin;	
	}	
	
	public void deleteStudent(Students student,Users user){
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();
		session.beginTransaction();
		session.delete(student);
		session.delete(user);
		session.getTransaction().commit();
		session.close();
		return;	  
	}
	
	public void deleteTeacher(Teachers teacher,Users user){
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();
		session.beginTransaction();
		session.delete(teacher);
		session.delete(user);
		session.getTransaction().commit();
		session.close();
		return;
	}

	public int getTeacherIDUsingUserID(int userID) throws SQLException, NamingException {
		
		
		DBConnection dbConnection = new DBConnection();
		
		ResultSet rs = dbConnection
				.executeQuery("select teacher_id from Teachers where user_id="+userID);

		int teacher_id=0;
		while (rs.next()) {
	
			teacher_id=rs.getInt("teacher_id");
			
		}
		rs.close();
		
		return teacher_id;
	}
	
	public int getStudentIDUsingUserID(int userID) throws SQLException, NamingException {
		
		
		DBConnection dbConnection = new DBConnection();
		
		ResultSet rs = dbConnection
				.executeQuery("select student_id from Students where user_id="+userID);

		int studentID=0;
		while (rs.next()) {
	
			studentID=rs.getInt("student_id");
			
		}
		rs.close();
		
		return studentID;
	}
	
}
